package com.carreirati.evaluation.domain;

import java.util.List;
import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.jpa.activerecord.RooJpaActiveRecord;
import org.springframework.roo.addon.json.RooJson;
import org.springframework.transaction.annotation.Transactional;

@RooJavaBean
@RooJpaActiveRecord(versionField = "", table = "question", schema = "dbo")
@RooDbManaged(automaticallyDelete = true)
@RooJson
public class Question {

    public static List<com.carreirati.evaluation.domain.Question> findQuestionsNotInAppraisal(Long appraisalId) {
        return entityManager().createNativeQuery("SELECT q.* FROM question q INNER JOIN knowledge_area ka ON q.knowledge_area_id = ka.id WHERE q.id NOT IN (SELECT question_id FROM appraisal_relationship ar WHERE is_active = 'Y'  AND ar.appraisal_id = '" + appraisalId + "') ORDER BY ka.description, q.description ", Question.class).getResultList();
    }
    
    @Transactional
    public Question merge() {
        if (this.entityManager == null) this.entityManager = entityManager();
        //there is a bug that is setting a "," in the end of question when we are doing the update
        this.setDescription(this.getDescription().substring(0, this.getDescription().length()-1));
        String sqlString = "UPDATE question SET knowledge_area_id = '"+ this.getKnowledgeAreaId().getId() +"', description = '"+ this.getDescription()+"', is_active ='"+ this.getIsActive()+"'"; 
        		sqlString += " ,expected_grade = '"+ this.getExpectedGrade()+"', extra_information  = '"+ this.getExtraInformation();
        		sqlString +="' WHERE id = '" + this.getId() + "'";
        this.entityManager.createNativeQuery(sqlString).executeUpdate();
        
                
        return this;
    }
    
    @Transactional
    public void remove() {
        if (this.entityManager == null) this.entityManager = entityManager();
        String sqlString = "";
        sqlString = "DELETE from appraisal_results WHERE question_id = '" + this.getId() + "'";
        this.entityManager.createNativeQuery(sqlString).executeUpdate();
        
        sqlString = "DELETE from appraisal_relationship WHERE question_id = '" + this.getId() + "'";
        this.entityManager.createNativeQuery(sqlString).executeUpdate();
        
        sqlString = "DELETE from question_parameter WHERE question_id = '" + this.getId() + "'";
        this.entityManager.createNativeQuery(sqlString).executeUpdate();
        
        sqlString = "DELETE from question WHERE id = '" + this.getId() + "'";
        this.entityManager.createNativeQuery(sqlString).executeUpdate();
        
    }
}
